﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Common;


namespace Web.SystemConfig.DataAccess
{
     public class DALSystem_Pluralist:DataAccessBase
    {
         internal int IsParentID()
         {
             string strsql = "SELECT COUNT(*) FROM P_Auth_ORG";
             int obj = (int)int.Parse(DBHelper.ExecuteScalar(strsql).ToString());
             return obj;
         }

         internal DataTable GetAllOrgData()
         {
             string strSql = "SELECT * FROM P_Auth_ORG where [Status]!='Org_Status--0'";
             return DBHelper.ExecuteDataSet(strSql).Tables[0];
         }
         internal DataTable GetUserByOrgID()
         {
             string strSql = "select User_ID,ORG_ID,User_Name from P_auth_user";
             return DBHelper.ExecuteDataSet(strSql).Tables[0];
         }
         #region add by 刘英男 获取所属单位名
         /// <summary>
         /// 根据单位id获取单位名
         /// </summary>
         /// <param name="orgid">单位id</param>
         /// <returns></returns>
         internal String GetNameByOrgId(string orgid)
         {
             string strSql = "select ORG_Name from p_auth_org where ORG_ID='" + orgid + "'";
             return DBHelper.ExecuteScalar(strSql).ToString();
         }
         #endregion
         #region add by 刘英男 获取用户名
         /// <summary>
         /// 根据用户id获取用户名
         /// </summary>
         /// <param name="userid">用户id</param>
         /// <returns></returns>
         internal String GetNameByUserId(string userid)
         {
             string strSql = "select User_Name from P_auth_user where User_ID='" + userid + "'";
             return DBHelper.ExecuteScalar(strSql).ToString();
         }
         #endregion

         #region add by 刘英男 单位序号
         /// <summary>
         /// 根据用户获取单位序号
         /// </summary>
         /// <param name="useridto">被授权用户</param>
         /// <returns>单位序号</returns>
         internal String GetOrgIdByUserId(string useridto)
         {
             string strSql = "select ORG_ID from P_auth_user where User_ID='" + useridto + "'";
             return DBHelper.ExecuteScalar(strSql).ToString();
         }
        #endregion
         #region add by 刘英男 单位序号
         /// <summary>
         /// 插入单位序号
         /// </summary>
         /// <param name="id">被授权主键</param>
         /// <param name="org_id">单位序号</param>
         /// <returns></returns>
         internal void insertOrgIdtoAuthority(string id, string org_id)
         {
             string sql="update oa_sys_roleauthority set org_id='"+org_id+"' where id='"+ id +"'";                
               DBHelper.ExecuteSql(sql);
         }
        #endregion
         #region add by 刘英男 删除用户授权
         /// <summary>
         /// 删除用户授权
         /// </summary>
         /// <param name="pkValue">被授权主键</param>
         /// <returns></returns>
         internal void DeleteRoleAuthority(string pkValue)
         {
             string sql = "delete from oa_sys_roleauthority where id='" + pkValue + "'";
             DBHelper.ExecuteSql(sql);
         }
         #endregion
        internal void ModifyUserInfo(string userName, DateTime userTime, string userSignId)
         {
             string strSql = "update OA_System_SignOptionList set CreateUser='" + userName + "',CreateTime='" + userTime + "'where SignOpId='"+userSignId+"' ";
             DBHelper.ExecuteSql(strSql);
         }
        internal void ModifyLastUserInfo(string userName, DateTime userTime, string userSignId)
         {
             string strSql = "update OA_System_SignOptionList set LastUpdateUser='" + userName + "',LastUpdateTime='" + userTime + "'where SignOpId='" + userSignId + "' ";
             DBHelper.ExecuteSql(strSql);
         }
        internal void modifyUserWordInfo(string pkvalue,string userName)
        {
            string strSql = "update OA_Search_KeyWord set KW_InputTime=getdate(),KW_IsInput=1,KW_InputAuthor='"+userName+"'where KW_ID='"+pkvalue+"'";
            DBHelper.ExecuteSql(strSql);
        }
        internal void ModifyOrgPluralistInfo(string userName, DateTime userTime, string userSignId)
        {
            string strSql = "update OA_System_OrgPlural set CreateUser='" + userName + "',CreateTime='" + userTime + "'where PassroundMenID='" + userSignId + "' ";
            DBHelper.ExecuteSql(strSql);
        }
        internal void ModifyLastOrgPluralistInfo(string userName, DateTime userTime, string userSignId)
        {
            string strSql = "update OA_System_OrgPlural set LastUpdateUser='" + userName + "',LastUpdateTime='" + userTime + "'where PassroundMenID='" + userSignId + "' ";
            DBHelper.ExecuteSql(strSql);
        }
        internal void ModifyPluralistInfo(string userName, DateTime userTime, string userSignId)
        {
            string strSql = "update OA_System_Pluralist set CreateUser='" + userName + "',CreateTime='" + userTime + "'where PassroundMenID='" + userSignId + "' ";
            DBHelper.ExecuteSql(strSql);
        }
        internal void ModifyLastPluralistInfo(string userName, DateTime userTime, string userSignId)
        {
            string strSql = "update OA_System_Pluralist set LastUpdateUser='" + userName + "',LastUpdateTime='" + userTime + "'where PassroundMenID='" + userSignId + "' ";
            DBHelper.ExecuteSql(strSql);
        }
        internal void UserAuthorityLoginTimesInfo(string userfrom, string userto)
        {
            string strSql = "update OA_Sys_RoleAuthority set LoginTimes=0 where useridfrom='" + userfrom + "' and useridto='" + userto + "' and deadline>=convert(varchar,getdate(),111)";
            DBHelper.ExecuteSql(strSql);
        }
        internal void UpdateUserAuthorityInfo(string userfrom, string userto, DateTime userTime)
        {
            string strSql = "update OA_Sys_RoleAuthority set LoginTimes=LoginTimes +1,lastlogintime='" + userTime + "' where useridfrom='" + userfrom + "' and useridto='" + userto + "' and deadline>=convert(varchar,getdate(),111)";
            DBHelper.ExecuteSql(strSql);
        }

        internal DataTable GetUserAuthorityInfo(string userfrom,string userto)
        {
            string strSql = "select * from OA_Sys_RoleAuthority where UserIdfrom = '" + userfrom + "' and UserIdto= '" + userto + "'and deadline>=convert(varchar,getdate(),111)";
            return this.DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        internal void DeleteAuthorityViewInfo(string pkValue)
        {
            string strSql = "delete from OA_Sys_RoleAuthority where Id='"+pkValue+"'";
            DBHelper.ExecuteSql(strSql);
        }
        #region add by 刘英男 20111102 职务名称
        /// <summary>
        /// 根据主键获取职务名称
        /// </summary>
        /// <param name="pkValue">主键</param>
        /// <returns>pkName</returns>
        internal String GetpkNameByPositionpkValueInfo(string pkValue)
        {
            string strSql = "select PositionName from P_BT_Position where Position_ID='" + pkValue + "'";
            return DBHelper.ExecuteScalar(strSql).ToString();
        }
        #endregion
        internal DataTable GetAuthorityInfo(string pkValue)
     {        
         string strSql = "select useridfrom,useridto,(select user_name from p_auth_user a,View_RoleAuthority b where a.user_id=b.useridfrom and id='" + pkValue + "') as userFrom,(select user_name from p_auth_user a,View_RoleAuthority b where a.user_id=b.useridto and id='" + pkValue + "') as userTo from View_RoleAuthority where id='" + pkValue + "'";
          return DBHelper.ExecuteDataSet(strSql).Tables[0];
     }
        internal DataTable GetOrgPluralInfo(string pkValue)
        {
            string strSql = "select DomOrgId,UnderDmOrgID,(select ORG_Name from p_auth_org a,oa_system_orgplural b where a.org_id=b.domorgid and passroundMenID ='"+pkValue+"')as DomorgName,(select ORG_Name from p_auth_org a,oa_system_orgplural b where a.org_id=b.underdmorgid and passroundMenID ='"+pkValue+"')as UnderDmOrgName from oa_system_orgplural where passroundMenID ='"+pkValue+"'";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        internal DataTable GetPluralistInfo(string pkValue)
        {
            string strSql = "select UserId,OrgId,(select User_Name from p_auth_user a,oa_system_pluralist b where a.user_id=b.userid and passroundMenID ='" + pkValue + "') as User_Name,(select Org_Name from p_auth_org a,oa_system_pluralist b where a.org_id=b.orgid and passroundMenID ='" + pkValue + "') as ORG_Name from oa_system_pluralist where passroundMenID ='" + pkValue + "'";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        internal DataTable GetSearchKeyWordInfo(string pkValue)
        {
            string strSql = "select KW_Content from OA_Search_KeyWord";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        internal DataTable GetSignOptionListInfo(string pkValue)
        {
            string strSql = "select signOpContent from OA_System_SignOptionList";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        #region add by lyn 检查职务是否被用户占用20120108
        /// <summary>
        ///  检查职务是否被用户占用
        /// </summary>
        /// <param name="pkValue">职务id</param>
        /// <returns>数量</returns>
        internal int IsPositionID(string pkValue)
        {
            string strsql = "select count(*) from p_auth_user where position_id='"+pkValue+"'";
            int obj = (int)int.Parse(DBHelper.ExecuteScalar(strsql).ToString());
            return obj;
        }
        #endregion

        #region 验证领导兼职，单位兼管 是否有重复数据 add by lyn 20120321
        /// <summary>
        /// 验证领导兼职，单位兼管 是否有重复数据
        /// </summary>
        /// <param name="tableName">数据库表名</param>
        /// <param name="orgid">数据库中单位id</param>
        /// <param name="userid">用户id</param>
        /// <param name="pkOrgId">数据库中时间</param>
        /// <param name="pkUserId">当前时间</param>
        /// <param name="primarykey">数据库主键</param>
        /// <param name="pkValue">主键</param>
        /// <returns></returns>
        internal DataTable getPluralistUserOrgInfo(string tableName, string orgid, string userid, string pkOrgId, string pkUserId)
        {
            string strSql="select * from " + tableName + " where " + orgid + "='" + pkOrgId + "' and " + userid + "='" + pkUserId + "'";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        #endregion

    }
}
